﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DrSoftsFrame.Servers;

namespace PreeRegisterDAL
{
    public class WorkCompareDAL
    {
        public static DataSet GetWorkCompareInfo(string workdate)
        {
            try
            {
                string sqlstr = string.Format(@"--创建科室排班号源ID串表
                                                create table #DeptWork
                                                (
                                                    ideptid int,
                                                    strWorkPlanid varchar(5000),
                                                    strWorkPlanDetailid varchar(8000),
                                                )

                                                --查询排班表
                                                select * into #opdworkplan from opdworkplan
                                                where convert(varchar(10),dstarttime,120)='{0}'
                                                and isnull(nMaxPreengageNum,0)>0 and isnull(nMaxDiagnoseNum,0)>0 

                                                --查询号源表
                                                select a.iDetailId,a.iWorkPlanID,b.ideptid into #opdworkplandetail 
                                                from opdworkplandetail a
                                                join #opdworkplan b on a.iworkplanid=b.iworkplanid

                                                --组合科室排班号源字符串
                                                declare @ideptid int,
                                                @striWorkPlanID varchar(5000),
                                                @stridetailID varchar(5000)

                                                set @striWorkPlanID=''
                                                set @stridetailID=''

                                                declare	deptlist cursor for 
                                                select ideptid from #opdworkplan
                                                group by ideptid
                                                open	deptlist
                                                fetch	deptlist into	@ideptid
                                                while	@@fetch_status = 0
                                                begin
	                                                select @striWorkPlanID+=convert(varchar(20),iWorkPlanID)+','  from #opdworkplan
	                                                where ideptid=@ideptid
                                                    order by iWorkPlanID
	
	                                                if len(@striWorkPlanID)>0
	                                                begin
	                                                    set @striWorkPlanID=substring(@striWorkPlanID,1,len(@striWorkPlanID)-1)
	                                                end
	
	                                                select @stridetailID+=convert(varchar(20),iDetailId)+',' from #opdworkplandetail
	                                                where ideptid=@ideptid
                                                    order by iDetailId
	
	                                                if len(@stridetailID)>0
	                                                begin
	                                                    set @stridetailID=substring(@stridetailID,1,len(@stridetailID)-1)
	                                                end
	
	                                                insert into #DeptWork(ideptid,strWorkPlanid,strWorkPlanDetailid)
	                                                values(@ideptid,@striWorkPlanID,@stridetailID)
	
	                                                set @striWorkPlanID=''
	                                                set @stridetailID=''
	                                                fetch	deptlist into	@ideptid
                                                end
                                                close	deptlist
                                                deallocate	deptlist

                                                --查询排班号源字符串
                                                select * from #DeptWork", workdate);
                ClinicDb mydb = new ClinicDb();
                DataSet ds = mydb.ExecuteSelect(sqlstr);
                if (ds == null || ds.Tables.Count < 1)
                {
                    return null;
                }
                ds.Tables[0].TableName = "WorkCompare";
                return ds;

            }
            catch (Exception err)
            {

                throw;
            }
        }
    }
}
